17. 格式内容与逻辑错误处理

# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
import pandas as pd  # 导入Pandas数据分析库
import numpy as np  # 导入NumPy数值计算库
# 从Excel文件读取数据存入df
df=pd.read_excel('https://huoran.oss-cn-shenzhen.aliyuncs.com/20230313/xls/1635115220651761664.xls',sheet_name='Sheet1')
df1=df.drop([247])  # 删除指定行或列
df1.dropna(how='all',inplace=True)  # 删除全部为空值的行
# 修改日期并转换数据类型
df1.iloc[201,0]='2022-11-04'
df1['日期']=pd.to_datetime(df1['日期'])  # 转换为日期时间格式
# 修改指定值
df1.loc[138,'科创50']=np.nan
# 填充缺失值
df1.fillna(method='ffill',inplace=True)
print(df1)  # 输出数据框数据

import pandas as pd
import numpy as np

# 创建含多种日期格式的数据
data = {
    'Date': ['2024-01-01',   # ISO标准
             '2024/01/02',   # 斜杠分隔
             '01-03-2024',   # 欧洲格式
             '2024.01.04',   # 点号分隔
             '20240105'],    # 紧凑格式
    'Price': [100.5, 101.2, 99.8, 102.3, 101.5],
    'Volume': [10000, 15000, 12000, 18000, 14000]
}
df_dates = pd.DataFrame(data)

print('原始数据(日期格式混乱):')
print(df_dates)
print('\n数据类型:')
print(df_dates.dtypes)

# errors='coerce': 无法解析的格式转为 NaT（Not a Time）
df_dates['Date_Parsed'] = pd.to_datetime(df_dates['Date'], errors='coerce')
print('自动解析后:')
print(df_dates[['Date', 'Date_Parsed']])
print('\n解析失败(转换为NaT):')
print(df_dates[df_dates['Date_Parsed'].isna()][['Date']])

# 方法2: format='mixed' 允许混合多种格式
df_dates['Date_Format2'] = pd.to_datetime(
    df_dates['Date'], format='mixed', errors='coerce'
)
print('使用format="mixed"参数:')
print(df_dates[['Date', 'Date_Format2']])

# 方法3: 对特殊格式明确指定
special_dates = pd.to_datetime('20240105', format='%Y%m%d')
print(f'\n特殊格式解析: {special_dates}')

import pandas as pd

data = {
    'Code': ['600001', '600002', '600003', '600004'],
    'Price': ['100.5', '105.2', 'error', '98.7'],
    'PE': ['15.3', 'N/A', '18.5', '22.1'],
    'Volume': ['10000', '15000', '12,000', '18,000']
}
df_numeric = pd.DataFrame(data)

print('原始数据(数值为文本):')
print(df_numeric)
print('\n数据类型:')
print(df_numeric.dtypes)

# 错误方式: astype(float) 遇到 'error' 会崩溃
# df_numeric['Price'] = df_numeric['Price'].astype(float)  # ValueError!

# 正确方式: pd.to_numeric + errors='coerce'
df_numeric['Price_Num'] = pd.to_numeric(df_numeric['Price'], errors='coerce')
df_numeric['PE_Num'] = pd.to_numeric(df_numeric['PE'], errors='coerce')

print('转换后(无法转换的变为NaN):')
print(df_numeric[['Code', 'Price', 'Price_Num', 'PE', 'PE_Num']])
print('\n转换后数据类型:')
print(df_numeric[['Price_Num', 'PE_Num']].dtypes)

# 先移除逗号，再转换为数值
df_numeric['Volume_Cleaned'] = df_numeric['Volume'].str.replace(',', '')
df_numeric['Volume_Num'] = pd.to_numeric(
    df_numeric['Volume_Cleaned'], errors='coerce'
)

print('处理千分位后:')
print(df_numeric[['Volume', 'Volume_Num']])

import pandas as pd
import numpy as np

data_dirty = {
    'Date': ['2024-01-01', '2024/01/02', '20240103',
             '2024-01-04', 'invalid'],
    'Code': ['600001.SH', '600002.SH', '000001.SZ',
             '600004.SH', '600005.SH'],
    'Open': ['¥10.5', '11.2', 'error', '12.5', '10.8'],
    'Close': ['10.8', '¥11.5', '12.3', '12.7', '10.5%'],
    'Volume': ['10000', '15,000', '12,000股', '18000', '20万'],
    'PE': ['15.3', 'N/A', '18.5', '>100', '22.1']
}
df_dirty = pd.DataFrame(data_dirty)

print('=== 原始数据(含多种格式错误) ===')
print(df_dirty)

df_clean = df_dirty.copy()

# 步骤1：日期格式统一
df_clean['Date'] = pd.to_datetime(df_clean['Date'], errors='coerce')
print('步骤1: 日期格式统一')
print(f'解析失败: {df_clean["Date"].isna().sum()} 条\n')

# 步骤2：移除货币符号并转换为数值
for col in ['Open', 'Close']:
    df_clean[col] = (df_clean[col].astype(str)
                     .str.replace('¥', '')
                     .str.replace('$', '')
                     .str.replace('%', ''))
    df_clean[col] = pd.to_numeric(df_clean[col], errors='coerce')

print('步骤2: 价格列清洗')
print(df_clean[['Open', 'Close']].head())

# 步骤3：成交量清洗（自定义函数处理多种单位）
def clean_volume(vol_str):
    '''清洗成交量数据，处理万/亿/股等单位'''
    if pd.isna(vol_str):
        return np.nan
    vol_str = str(vol_str).strip()
    if '万' in vol_str:
        return float(vol_str.replace('万', '').replace(',', '')) * 10000
    elif '亿' in vol_str:
        return float(vol_str.replace('亿', '').replace(',', '')) * 100000000
    elif '股' in vol_str:
        return float(vol_str.replace('股', '').replace(',', ''))
    else:
        return float(vol_str.replace(',', ''))

df_clean['Volume_Num'] = df_clean['Volume'].apply(clean_volume)
print('步骤3: 成交量清洗')
print(df_clean[['Volume', 'Volume_Num']])

# 步骤4：PE比率清洗
df_clean['PE'] = df_clean['PE'].astype(str).str.replace('>', '')
df_clean['PE'] = pd.to_numeric(df_clean['PE'], errors='coerce')
print('\n步骤4: PE比率清洗')
print(df_clean[['PE']].head())

print('=== 清洗后数据类型 ===')
print(df_clean.dtypes)

print('\n=== 清洗后数据 ===')
print(df_clean.head())

print('\n=== 数据质量报告 ===')
print(f'总行数: {len(df_clean)}')
print(f'完整行数: {df_clean.dropna().shape[0]}')
print('\n各列缺失值统计:')
print(df_clean.isnull().sum())

import pandas as pd
import numpy as np

np.random.seed(42)
dates = pd.date_range('2024-01-01', periods=20)
normal_prices = 100 + np.random.randn(20).cumsum() * 0.5

# 人为添加异常值
prices = normal_prices.copy()
prices[5] = -50      # 异常1：负价格
prices[10] = 5000    # 异常2：异常高价
prices[15] = 0.01    # 异常3：接近零

df_prices = pd.DataFrame({
    'Date': dates, 'Price': prices,
    'Volume': np.random.randint(10000, 100000, 20)
})

# 异常检测规则
rule1 = df_prices['Price'] <= 0
df_prices['Prev_Price'] = df_prices['Price'].shift(1)
rule2 = df_prices['Price'] > df_prices['Prev_Price'] * 3
rule3 = df_prices['Price'] < df_prices['Prev_Price'] * 0.3
anomaly_mask = rule1 | rule2 | rule3

print(f'异常记录数: {anomaly_mask.sum()}')
anomaly_records = df_prices[anomaly_mask].copy()
anomaly_records['Reason'] = ''
anomaly_records.loc[rule1, 'Reason'] = '非正价格'
anomaly_records.loc[rule2, 'Reason'] = '异常涨幅'
anomaly_records.loc[rule3, 'Reason'] = '异常跌幅'
print('\n异常记录详情:')
print(anomaly_records[['Date', 'Price', 'Reason']])

# 策略1：删除异常记录
df_clean_drop = df_prices[~anomaly_mask].copy()
print('策略1: 删除异常记录')
print(f'原始记录数: {len(df_prices)}, 清洗后: {len(df_clean_drop)}\n')

# 策略2：用前值填充
df_clean_ffill = df_prices.copy()
df_clean_ffill.loc[anomaly_mask, 'Price'] = np.nan
df_clean_ffill['Price'] = df_clean_ffill['Price'].ffill()
print('策略2: 前值填充')
print(df_clean_ffill.loc[anomaly_mask, ['Date', 'Price']])

# 策略3：标记但不删除（保留用于人工审核）
df_marked = df_prices.copy()
df_marked['Is_Anomaly'] = anomaly_mask
print('\n策略3: 标记异常')
print(df_marked[df_marked['Is_Anomaly']][['Date', 'Price']])

import pandas as pd
import numpy as np

np.random.seed(42)
n_days = 50
df_vol = pd.DataFrame({
    'Date': pd.date_range('2024-01-01', periods=n_days),
    'Price': 100 + np.random.randn(n_days).cumsum() * 0.5,
    'Volume': np.random.randint(1000000, 10000000, n_days),
    'Turnover': np.random.uniform(100000000, 1000000000, n_days)
})

# 添加异常成交量
df_vol.loc[10, 'Volume'] = 100000000   # 异常高
df_vol.loc[25, 'Volume'] = 100         # 异常低
df_vol.loc[40, 'Volume'] = 0           # 零成交

# 异常检测规则
rule1 = df_vol['Volume'] <= 0
volume_mean = df_vol['Volume'].mean()
rule2 = df_vol['Volume'] > volume_mean * 10
rule3 = df_vol['Volume'] < volume_mean * 0.01
anomaly_vol = rule1 | rule2 | rule3

print(f'历史均值: {volume_mean:,.0f}')
print(f'异常记录数: {anomaly_vol.sum()}')
vol_anomalies = df_vol[anomaly_vol].copy()
vol_anomalies['Reason'] = ''
vol_anomalies.loc[rule1, 'Reason'] = '非正成交量'
vol_anomalies.loc[rule2, 'Reason'] = '异常高成交量'
vol_anomalies.loc[rule3, 'Reason'] = '异常低成交量'
print('\n异常记录:')
print(vol_anomalies[['Date', 'Volume', 'Reason']])

# 估计成交额 = 价格 × 成交量 × 100（每手100股）
df_vol['Est_Turnover'] = df_vol['Price'] * df_vol['Volume'] * 100
df_vol['Turnover_Ratio'] = df_vol['Est_Turnover'] / df_vol['Turnover']

print('成交额验证(比例应在合理范围内):')
print(df_vol[['Date', 'Turnover', 'Est_Turnover',
              'Turnover_Ratio']].head(10))

# 比例 > 2 或 < 0.5：估计值与实际值相差超过2倍
turnover_anomaly = (
    (df_vol['Turnover_Ratio'] > 2) | (df_vol['Turnover_Ratio'] < 0.5)
)
print(f'\n成交额比例异常记录: {turnover_anomaly.sum()} 条')

import pandas as pd
import numpy as np

np.random.seed(42)
n_days = 10
index_values = [3000 + i * 10 + np.random.randn() * 5
                for i in range(n_days)]
returns = pd.Series(index_values).pct_change().tolist()

df_index = pd.DataFrame({
    'Date': pd.date_range('2024-01-01', periods=n_days),
    'Index_Close': index_values,
    'Index_Open': [v + np.random.uniform(-10, 10) for v in index_values],
    'Daily_Return': returns,
    'Volume': np.random.randint(100000000, 500000000, n_days)
})

# 人为添加不一致
df_index.loc[5, 'Index_Close'] = 3100
df_index.loc[3, 'Daily_Return'] = -0.5

# 一致性检验
consist1 = df_index['Index_Close'] <= 0
consist2 = ((df_index['Index_Open'] / df_index['Index_Close'] > 1.2) |
            (df_index['Index_Open'] / df_index['Index_Close'] < 0.8))
df_index['Calculated_Return'] = df_index['Index_Close'].pct_change()
consist3 = (df_index['Calculated_Return'] -
            df_index['Daily_Return']).abs() > 0.01
inconsistency_mask = consist1 | consist2 | consist3

print(f'不一致记录数: {inconsistency_mask.sum()}')
result = df_index[inconsistency_mask][
    ['Date', 'Index_Close', 'Daily_Return', 'Calculated_Return']
]
print('\n不一致详情:')
print(result)

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

np.random.seed(42)
n = 50
dates = pd.date_range('2024-01-01', periods=n)
index_values = 3000 + np.arange(n) * 5 + np.random.randn(n) * 20

df_index = pd.DataFrame({
    'Date': dates,
    'Open': index_values + np.random.uniform(-5, 5, n),
    'High': index_values + np.random.uniform(0, 10, n),
    'Low': index_values - np.random.uniform(0, 10, n),
    'Close': index_values,
    'Volume': np.random.randint(100000000, 500000000, n),
    'Amount': np.random.randint(
        100000000000, 500000000000, n, dtype=np.int64)
})

# 人为添加错误
df_index.loc[5, 'Close'] = '3,250.5'          # 千分位
df_index.loc[10, 'Volume'] = '200000000股'     # 含单位
df_index.loc[15, 'Close'] = -3000              # 负价格
df_index.loc[20, 'High'] = df_index.loc[20, 'Low'] - 100  # High < Low
df_index.loc[25, 'Low'] = 3500                 # Low > Close
df_index.loc[30, 'Volume'] = 10000000000       # 异常高成交量
df_index.loc[35, 'Close'] = 5000               # 异常高指数

print('=== 原始数据预览 ===')
print(df_index.head(10))
print('\n数据类型:')
print(df_index.dtypes)

df_fixed = df_index.copy()
print('=== 格式错误修复 ===\n')

# 修复1：移除千分位
if df_fixed['Close'].dtype == 'object':
    df_fixed['Close'] = df_fixed['Close'].astype(str).str.replace(',', '')
    print('步骤1: 移除价格列的千分位逗号')

# 修复2：转换为数值
numeric_cols = ['Open', 'High', 'Low', 'Close']
for col in numeric_cols:
    if df_fixed[col].dtype == 'object':
        df_fixed[col] = pd.to_numeric(df_fixed[col], errors='coerce')
        print(f'步骤2: 将{col}转换为数值类型')

# 修复3：成交量清洗
if df_fixed['Volume'].dtype == 'object':
    def clean_vol(vol):
        if pd.isna(vol):
            return np.nan
        vol_str = str(vol).replace('股', '').replace(',', '').replace('万', '0000')
        try:
            return float(vol_str)
        except:
            return np.nan
    df_fixed['Volume'] = df_fixed['Volume'].apply(clean_vol)
    print('步骤3: 清洗成交量格式')

print(f'\n格式修复后数据类型:')
print(df_fixed.dtypes)

print('=== 逻辑错误检测与修复 ===\n')
errors = []

# 规则1：价格必须为正
error_price_negative = df_fixed['Close'] <= 0
if error_price_negative.any():
    errors.append(('负价格', error_price_negative.sum()))
    df_fixed.loc[error_price_negative, 'Close'] = np.nan
    df_fixed['Close'] = df_fixed['Close'].ffill()
    print(f'规则1: 修复{error_price_negative.sum()}条负价格记录')

# 规则2：High >= Close, Low <= Close
error_high_low = ((df_fixed['High'] < df_fixed['Close']) |
                  (df_fixed['Low'] > df_fixed['Close']))
if error_high_low.any():
    errors.append(('高低价错误', error_high_low.sum()))
    df_fixed.loc[df_fixed['High'] < df_fixed['Close'], 'High'] = \
        df_fixed['Close']
    df_fixed.loc[df_fixed['Low'] > df_fixed['Close'], 'Low'] = \
        df_fixed['Close']
    print(f'规则2: 修复{error_high_low.sum()}条高低价错误记录')

# 规则3：单日涨跌幅不超过15%
df_fixed['Prev_Close'] = df_fixed['Close'].shift(1)
df_fixed['Daily_Change'] = (
    (df_fixed['Close'] - df_fixed['Prev_Close']) / df_fixed['Prev_Close']
)
error_excess_change = df_fixed['Daily_Change'].abs() > 0.15
if error_excess_change.any():
    errors.append(('异常涨跌', error_excess_change.sum()))
    print(f'规则3: 发现{error_excess_change.sum()}条异常涨跌记录')
    print(df_fixed[error_excess_change][
        ['Date', 'Close', 'Prev_Close', 'Daily_Change']])

# 规则4：成交量异常
volume_median = df_fixed['Volume'].median()
error_volume = ((df_fixed['Volume'] > volume_median * 10) |
                (df_fixed['Volume'] < volume_median * 0.01))
if error_volume.any():
    errors.append(('异常成交量', error_volume.sum()))
    print(f'规则4: 发现{error_volume.sum()}条异常成交量记录')

print(f'\n=== 错误汇总 ===')
for error_type, count in errors:
    print(f'{error_type}: {count}条')